package dao;

import model.Reservation;
import model.User;
import model.Seat;
import util.DatabaseConnection;

import java.sql.*;
import java.time.LocalDate;

import java.util.ArrayList;
import java.util.List;

/**
 * 预约数据访问对象
 */
public class ReservationDAO {

    /**
     * 插入新预约
     */
    public boolean insertReservation(Reservation reservation) throws SQLException {
        String sql = "INSERT INTO reservations (user_id, seat_id, reservation_date, start_time, end_time, status, create_time) "
                +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            stmt.setInt(1, reservation.getUserId());
            stmt.setInt(2, reservation.getSeatId());
            stmt.setDate(3, Date.valueOf(reservation.getReservationDate()));
            stmt.setTime(4, Time.valueOf(reservation.getStartTime()));
            stmt.setTime(5, Time.valueOf(reservation.getEndTime()));
            stmt.setInt(6, reservation.getStatus());
            stmt.setTimestamp(7, Timestamp.valueOf(reservation.getCreateTime()));

            int result = stmt.executeUpdate();
            if (result > 0) {
                try (ResultSet rs = stmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        reservation.setReservationId(rs.getInt(1)); // 设置生成的ID
                    }
                }
                return true;
            }
            return false;
        }
    }

    /**
     * 检查用户在同一时间段是否已有预约（防止占座）
     */
    public boolean hasUserTimeSlotConflict(Reservation reservation) throws SQLException {
        String sql = "SELECT COUNT(*) FROM reservations WHERE user_id = ? AND reservation_date = ? " +
                "AND status = 0 AND ((start_time <= ? AND end_time > ?) OR (start_time < ? AND end_time >= ?))";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, reservation.getUserId());
            stmt.setDate(2, Date.valueOf(reservation.getReservationDate()));
            stmt.setTime(3, Time.valueOf(reservation.getStartTime()));
            stmt.setTime(4, Time.valueOf(reservation.getStartTime()));
            stmt.setTime(5, Time.valueOf(reservation.getEndTime()));
            stmt.setTime(6, Time.valueOf(reservation.getEndTime()));

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        }
        return false;
    }

    /**
     * 检查座位时间冲突
     */
    public boolean hasTimeConflict(Reservation reservation) throws SQLException {
        String sql = "SELECT COUNT(*) FROM reservations WHERE seat_id = ? AND reservation_date = ? " +
                "AND status = 0 AND ((start_time <= ? AND end_time > ?) OR (start_time < ? AND end_time >= ?))";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, reservation.getSeatId());
            stmt.setDate(2, Date.valueOf(reservation.getReservationDate()));
            stmt.setTime(3, Time.valueOf(reservation.getStartTime()));
            stmt.setTime(4, Time.valueOf(reservation.getStartTime()));
            stmt.setTime(5, Time.valueOf(reservation.getEndTime()));
            stmt.setTime(6, Time.valueOf(reservation.getEndTime()));

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        }
        return false;
    }

    /**
     * 取消预约
     */
    public boolean cancelReservation(int reservationId) throws SQLException {
        String sql = "UPDATE reservations SET status = 1 WHERE reservation_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, reservationId);
            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 获取用户的预约记录
     */
    public List<Reservation> getUserReservations(int userId) throws SQLException {
        String sql = "SELECT r.*, u.student_id, u.name as user_name, s.seat_number, s.floor " +
                "FROM reservations r " +
                "LEFT JOIN users u ON r.user_id = u.user_id " +
                "LEFT JOIN seats s ON r.seat_id = s.seat_id " +
                "WHERE r.user_id = ? ORDER BY r.create_time DESC";

        List<Reservation> reservations = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                reservations.add(mapResultSetToReservation(rs));
            }
        }
        return reservations;
    }

    /**
     * 获取所有预约记录
     */
    public List<Reservation> getAllReservations() throws SQLException {
        String sql = "SELECT r.*, u.student_id, u.name as user_name, s.seat_number, s.floor " +
                "FROM reservations r " +
                "LEFT JOIN users u ON r.user_id = u.user_id " +
                "LEFT JOIN seats s ON r.seat_id = s.seat_id " +
                "ORDER BY r.create_time DESC";

        List<Reservation> reservations = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                reservations.add(mapResultSetToReservation(rs));
            }
        }
        return reservations;
    }

    /**
     * 根据筛选条件获取预约记录
     */
    public List<Reservation> getFilteredReservations(String dateFilter, String statusFilter) throws SQLException {
        StringBuilder sql = new StringBuilder(
                "SELECT r.*, u.student_id, u.name as user_name, s.seat_number, s.floor " +
                        "FROM reservations r " +
                        "LEFT JOIN users u ON r.user_id = u.user_id " +
                        "LEFT JOIN seats s ON r.seat_id = s.seat_id WHERE 1=1");

        List<Object> params = new ArrayList<>();

        // 日期筛选
        if (!"全部日期".equals(dateFilter)) {
            if ("今天".equals(dateFilter)) {
                sql.append(" AND r.reservation_date = ?");
                params.add(Date.valueOf(LocalDate.now()));
            } else if ("明天".equals(dateFilter)) {
                sql.append(" AND r.reservation_date = ?");
                params.add(Date.valueOf(LocalDate.now().plusDays(1)));
            } else if ("本周".equals(dateFilter)) {
                sql.append(" AND r.reservation_date BETWEEN ? AND ?");
                LocalDate now = LocalDate.now();
                LocalDate weekStart = now.minusDays(now.getDayOfWeek().getValue() - 1);
                LocalDate weekEnd = weekStart.plusDays(6);
                params.add(Date.valueOf(weekStart));
                params.add(Date.valueOf(weekEnd));
            }
        }

        // 状态筛选
        if (!"全部状态".equals(statusFilter)) {
            sql.append(" AND r.status = ?");
            switch (statusFilter) {
                case "有效":
                    params.add(0);
                    break;
                case "已取消":
                    params.add(1);
                    break;
                case "已完成":
                    params.add(2);
                    break;
                case "违规":
                    params.add(3);
                    break;
            }
        }

        sql.append(" ORDER BY r.create_time DESC");

        List<Reservation> reservations = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                reservations.add(mapResultSetToReservation(rs));
            }
        }
        return reservations;
    }

    /**
     * 搜索预约记录
     */
    public List<Reservation> searchReservations(String keyword) throws SQLException {
        String sql = "SELECT r.*, u.student_id, u.name as user_name, s.seat_number, s.floor " +
                "FROM reservations r " +
                "LEFT JOIN users u ON r.user_id = u.user_id " +
                "LEFT JOIN seats s ON r.seat_id = s.seat_id " +
                "WHERE u.student_id LIKE ? OR u.name LIKE ? OR s.seat_number LIKE ? " +
                "ORDER BY r.create_time DESC";

        List<Reservation> reservations = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            String searchKeyword = "%" + keyword + "%";
            stmt.setString(1, searchKeyword);
            stmt.setString(2, searchKeyword);
            stmt.setString(3, searchKeyword);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                reservations.add(mapResultSetToReservation(rs));
            }
        }
        return reservations;
    }

    /**
     * 根据日期获取预约记录
     */
    public List<Reservation> getReservationsByDate(String date) throws SQLException {
        String sql = "SELECT r.*, u.student_id, u.name as user_name, s.seat_number, s.floor " +
                "FROM reservations r " +
                "LEFT JOIN users u ON r.user_id = u.user_id " +
                "LEFT JOIN seats s ON r.seat_id = s.seat_id " +
                "WHERE r.reservation_date = ? ORDER BY r.start_time";

        List<Reservation> reservations = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setDate(1, Date.valueOf(date));
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                reservations.add(mapResultSetToReservation(rs));
            }
        }
        return reservations;
    }

    /**
     * 检查并更新过期预约的状态
     * 根据签到情况将过期预约标记为"已完成"或"违规"
     */
    public void updateExpiredReservationsStatus() throws SQLException {
        String sql = "UPDATE reservations r " +
                "LEFT JOIN attendance_records ar ON r.reservation_id = ar.reservation_id " +
                "SET r.status = CASE " +
                "    WHEN ar.is_checked_in = TRUE THEN 2 " + // 已签到，标记为已完成
                "    ELSE 3 " + // 未签到，标记为违规
                "END " +
                "WHERE r.status = 0 " + // 仅处理状态为有效的预约
                "AND CONCAT(r.reservation_date, ' ', r.end_time) < NOW()"; // 预约结束时间已过

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            int updatedCount = stmt.executeUpdate();
            System.out.println("已更新 " + updatedCount + " 个过期预约的状态");
        }
    }

    /**
     * 将ResultSet映射为Reservation对象
     */
    private Reservation mapResultSetToReservation(ResultSet rs) throws SQLException {
        Reservation reservation = new Reservation();
        reservation.setReservationId(rs.getInt("reservation_id"));
        reservation.setUserId(rs.getInt("user_id"));
        reservation.setSeatId(rs.getInt("seat_id"));
        reservation.setReservationDate(rs.getDate("reservation_date").toLocalDate());
        reservation.setStartTime(rs.getTime("start_time").toLocalTime());
        reservation.setEndTime(rs.getTime("end_time").toLocalTime());
        reservation.setStatus(rs.getInt("status"));

        Timestamp createTime = rs.getTimestamp("create_time");
        if (createTime != null) {
            reservation.setCreateTime(createTime.toLocalDateTime());
        }

        // 设置关联的用户信息
        User user = new User();
        user.setUserId(rs.getInt("user_id"));
        user.setStudentId(rs.getString("student_id"));
        user.setName(rs.getString("user_name"));
        reservation.setUser(user);

        // 设置关联的座位信息
        Seat seat = new Seat();
        seat.setSeatId(rs.getInt("seat_id"));
        seat.setSeatNumber(rs.getString("seat_number"));
        seat.setFloor(rs.getInt("floor"));
        reservation.setSeat(seat);

        return reservation;
    }
}
